IF exists(Select 1 from sys.objects where [name] = 'SPGetRol' and [type] = 'P')
BEGIN
        drop procedure SPGetRol
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              Carlos Cabanas
-- Create date: 12-Dic-2012
-- Description: Obtiene las Categorias
-- =============================================
CREATE PROCEDURE SPGetRol
        -- Add the parameters for the stored procedure here
        @ID int = null,
        @Nombre nvarchar(100) = null,
        @Habilitar bit = null 
        
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        SELECT 
                Role_PK as ID,
                Role_Nombre as Nombre,
                Role_Habilitado as Hab

        FROM tblRoles
        WHERE   (@ID is null or @ID = Role_PK)
                        AND
                        (@Nombre is null or Role_Nombre like '%'+@Nombre+'%') 
                        AND
                        (@Habilitar is null or @Habilitar = Role_Habilitado)

        Order by Role_PK

        SET NOCOUNT OFF;
END
GO

IF exists(Select 1 from sys.objects where [name] = 'SPGetRol' and [type] = 'P')
BEGIN
        PRINT 'SP Creado: SPGetRol '
END
GO

IF exists(Select 1 from sys.objects where [name] = 'SPGetUsuario' and [type] = 'P')
BEGIN
        drop procedure SPGetUsuario
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              Carlos Cabanas
-- Create date: 12-Dic-2012
-- Description: Obtiene las Categorias
-- =============================================
CREATE PROCEDURE SPGetUsuario
        -- Add the parameters for the stored procedure here
        @ID int = null,
        @Search nvarchar(100) = null,
        @Habilitar bit = null,
        @ValidarUsuario bit = 0
        
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        SELECT 
                Usr_PK as ID,
                Usr_UsrName as UserName,
                Usr_Pwd as Password,
                Usr_Nombre as Nombre,
                Usr_Apellido as Apellido,
                Usr_Email as Email,
                Usr_Role_FK as RolId,
                Usr_Habilitado as Habilitado
        FROM dbo.tblUsuarios
        WHERE   (@ID is null or @ID = Usr_PK)
                        AND
                        (
                                (@ValidarUsuario = 1 and @Search like Usr_UsrName)
                                OR
                                ( 
                                        @ValidarUsuario = 0 
                                        AND
                                        (
                                                (@Search is null or Usr_Nombre like '%'+@Search+'%' or Usr_Apellido like '%'+@Search+'%' or Usr_UsrName like '%'+@Search+'%') 
                                                AND
                                                (@Habilitar is null or @Habilitar = Usr_Habilitado)
                                        )
                                        
                                )
                        )

        Order by Usr_PK

        SET NOCOUNT OFF;
END
GO

IF exists(Select 1 from sys.objects where [name] = 'SPGetUsuario' and [type] = 'P')
BEGIN
        PRINT 'SP Creado: SPGetUsuario '
END
GO

IF exists(Select 1 from sys.objects where [name] = 'SPLoggingValidate' and [type] = 'P')
BEGIN
        drop procedure SPLoggingValidate
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              Carlos Cabanas
-- Create date: 11-Dic-2012
-- Description: Validate Logging
-- =============================================
CREATE PROCEDURE SPLoggingValidate
        -- Add the parameters for the stored procedure here
        @Usuario nvarchar(100), 
        @Password nvarchar(100)
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        SELECT 1  
                FROM tblUsuarios
                where Usr_UsrName like @Usuario and Usr_Pwd = @Password and Usr_Habilitado = 1
        SET NOCOUNT OFF;
END
GO

IF exists(Select 1 from sys.objects where [name] = 'SPLoggingValidate' and [type] = 'P')
BEGIN
        PRINT 'SP Creado: SPLoggingValidate '
END
GO

IF exists(Select 1 from sys.objects where [name] = 'SPUpdateInsetUsuario' and [type] = 'P')
BEGIN
        drop procedure SPUpdateInsetUsuario
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              Carlos Cabanas
-- Create date: 12-Dic-2012
-- Description: Obtiene las Categorias
-- =============================================
CREATE PROCEDURE SPUpdateInsetUsuario
        -- Add the parameters for the stored procedure here
        @ID int = null,
        @Username nvarchar(100),
        @Psw nvarchar(100),
        @Nombre nvarchar(500),
        @Apellido nvarchar(500),
        @Email nvarchar(max),
        @RoleId int,
        @Habilitar bit 
        
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        
        IF(@ID is null)
        BEGIN
                INSERT INTO [tblUsuarios]
           ([Usr_UsrName]
           ,[Usr_Pwd]
           ,[Usr_Nombre]
           ,[Usr_Apellido]
           ,[Usr_Email]
           ,[Usr_Role_FK]
           ,[Usr_Habilitado])
     VALUES
           (@Username
           ,@Psw
           ,@Nombre
           ,@Apellido
           ,@Email
           ,@RoleId
           ,@Habilitar)
                
                SELECT @ID = @@IDENTITY
        END
        ELSE
        BEGIN
                UPDATE [tblUsuarios]
                   SET [Usr_UsrName] = @Username
                          ,[Usr_Pwd] = @Psw
                          ,[Usr_Nombre] = @Nombre
                          ,[Usr_Apellido] = @Apellido
                          ,[Usr_Email] = @Email
                          ,[Usr_Role_FK] = @RoleId
                          ,[Usr_Habilitado] = @Habilitar
                 WHERE Usr_PK = @ID
        END

        SELECT @ID as id

        SET NOCOUNT OFF;
END
GO

IF exists(Select 1 from sys.objects where [name] = 'SPUpdateInsetUsuario' and [type] = 'P')
BEGIN
        PRINT 'SP Creado: SPUpdateInsetUsuario '
END
GO


IF exists(Select 1 from sys.objects where [name] = 'SPUpdateInsetRole' and [type] = 'P')
BEGIN
        drop procedure SPUpdateInsetRole
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              Carlos Cabanas
-- Create date: 12-Dic-2012
-- Description: Obtiene las Categorias
-- =============================================
CREATE PROCEDURE SPUpdateInsetRole
        -- Add the parameters for the stored procedure here
        @ID int = null,
        @Nombre nvarchar(100),
        @Habilitar bit 
        
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        
        IF(@ID is null)
        BEGIN
                INSERT INTO tblRoles
                ( Role_Nombre,Role_Habilitado)
                 values
                (@Nombre, @Habilitar)
                
                SELECT @ID = @@IDENTITY
        END
        ELSE
        BEGIN
                UPDATE tblRoles
                        SET Role_Nombre = ISNULL(@Nombre, Role_Nombre),
                                
                                Role_Habilitado = @Habilitar
                WHERE Role_PK = @ID
        END

        SELECT @ID as id

        SET NOCOUNT OFF;
END
GO

IF exists(Select 1 from sys.objects where [name] = 'SPUpdateInsetRole' and [type] = 'P')
BEGIN
        PRINT 'SP Creado: SPUpdateInsetRole '
END
GO